/**
 * 
 */
package com.jm.web.action;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.net.URI;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;

import javax.servlet.http.Cookie;

import org.apache.commons.httpclient.Header;
import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.NameValuePair;
import org.apache.commons.httpclient.methods.GetMethod;
import org.apache.commons.httpclient.methods.PostMethod;
import org.apache.commons.httpclient.params.HttpClientParams;

import jxl.Workbook;
import jxl.biff.DisplayFormat;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.jm.utils.StringHelper;
import com.jm.web.BasePage;
import com.jm.web.data.BaseDataEntity;
import com.jm.web.form.Export;
import com.jm.web.form.support.FormItem;
import com.jm.web.form.table.Table;
import com.jm.web.form.table.Tbody;
import com.jm.web.form.table.Td;
import com.jm.web.form.table.Tfoot;
import com.jm.web.form.table.Thead;
import com.jm.web.form.table.Tr;
import com.jm.web.tags.ExportTag;
import com.jm.web.tags.table.TableTag;
import com.jm.web.tags.table.TbodyTag;
import com.jm.web.tags.table.TfootTag;
import com.jm.web.tags.table.TheadTag;

/**
 * @author yanjie
 * Oct 31, 2011 12:04:37 PM
 */
public class BaseExportActionHelper {
	protected BasePage page = null;
	Vector<Export> nodes = null;
	Map<String ,DisplayFormat> numberMap = null ;
	Map<String,Map<Integer,String>> gcolsMap = new HashMap<String,Map<Integer,String>>();
	WritableWorkbook wb1 = null;
	String curSheetName = "";
	int x1 = 0;//x 軸
    int y1 = 0;//y 軸
     
	int ox1 = 0;//x 起點座標
    int oy1 = 0;//y 起點座標
    Vector<String> ac1 = new Vector<String>() ;//All Cell 記錄哪些單點格被占用
    protected long cur = 0;
    Connection con = null;
	Statement stmnt = null;
	StringBuffer fileName = null;
	
	public void openCon(){
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url="jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};readOnly=false;DBQ="+fileName.toString();
			con=DriverManager.getConnection(url);
			stmnt = con.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void closeCon(){
		if (stmnt != null)
			try {
				stmnt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		
		if (con != null)
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	
	/**
	 * @param tag
	 * 初始化xls文件
	 * @throws IOException 
	 */
	public void initFile(ExportTag exportTag,Export export){
		cur = System.currentTimeMillis();
		fileName = new StringBuffer(page.getUtils().getTempDataPath());
	    fileName.append(page.getUtils().getHrNum()).append(".xls");
	    try {
	    	if(wb1 == null){
	    		File file = new File(fileName.toString());
	    		if(!file.getParentFile().exists())file.getParentFile().mkdirs();
	    		file.deleteOnExit();
//				WorkbookSettings ws = new WorkbookSettings();
//		        ws.setUseTemporaryFileDuringWrite(true);
	    		wb1 = Workbook.createWorkbook(file);
	    	}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		WritableSheet sheet = wb1.getSheet(export.getSheetName()); 
		
    	if(sheet == null){
    		curSheetName = export.getSheetName();
    		sheet = wb1.createSheet(export.getSheetName(),wb1.getSheets().length);
    	}
    	
    	BaseDataEntity data = export.getExportConfig();
    	//x1 = data.GetParamIntValue("x",sheet.getColumns());
    	x1 = 0;
    	y1 = data.GetParamIntValue("y",sheet.getRows());
    	
    	//ox1 = data.GetParamIntValue("x",sheet.getColumns());
    	ox1 =0;
    	oy1 = data.GetParamIntValue("y",sheet.getRows());
	}
	
	/**
	 * @param table
	 * 导出表
	 */
	public void exportTable(TableTag tableTag,Table table){
		
	}
	
	/**
	 * @param theadTag
	 * @param thead
	 * 导出表头
	 */
	public void exportThead(TheadTag theadTag,Thead thead){
		if(thead == null)
			return ;
		try{
			TableTag tableTag = theadTag.getTableTag();
			ExportTag exportTag = tableTag.getExportTag();
			WritableSheet sheet = wb1.getSheet(exportTag.GetExtValue("sheetname","")); 
			curSheetName = exportTag.GetExtValue("sheetname","");
			
			Map<Integer,BaseDataEntity> columnsConfig = tableTag.getColumnsConfig();//表格列配置
			BaseDataEntity tableConfig = tableTag.getTable().getExportConfig(); //表配置
			
			Map<Integer,String> colsMap = new HashMap<Integer,String>(); 
			//循環thead 取出其中的tr
			for (int j = 0;thead.getItems() != null && j < thead.getItems().size(); j++) {
				if(thead.getItems().get(j) instanceof Tr){
					Tr tr = (Tr)thead.getItems().get(j);//tr
					BaseDataEntity trConfig = tr.getExportConfig();//tr配置
					tableConfig.CopyTo(trConfig,false,false);
					
					x1 = ox1;
					if(y1-1 >= 0){
						for (int i = 0; i <= tr.getItems().size(); i++) {
							ac1.remove(getPostion(i,y1-1));
						}
					}
					for (int k = 0;tr.getItems() != null && k < tr.getItems().size(); k++) {
						if(tr.getItems().get(k) instanceof Td){
							Td td = (Td)tr.getItems().get(k);//td
							
							//1. 判斷 x ,y 座標有沒有補占用,並計算出新的x,y座標
							int[] ay = calPostion(ac1,x1,y1);
							x1 = ay[0];
							y1 = ay[1];
							
							//2 處理表格配置
							BaseDataEntity tdConfig = td.getExportConfig();
							
							//3处理td宽度
							int width = 0;
							width = tdConfig.GetParamIntValue("width",0);
							if(width > 0){
								sheet.setColumnView(x1,width);
							}
							
							
							for(int index=0;index<td.getColspan();index++){
								columnsConfig.put(x1+index,tdConfig);
							}
							
							//新建临时配置 原tdconfig是公用的所以不能修改
							BaseDataEntity temp = new BaseDataEntity();
							tdConfig.CopyTo(temp,true,false);
							trConfig.CopyTo(temp,false,false);
							
							//4 判斷是否需要合併單元格
							int colspan = td.getColspan();
							int rowspan = td.getRowspan();
							if(colspan > 1 || rowspan >1){
								sheet.mergeCells(x1,y1,x1+colspan-1,y1+rowspan-1);
								for(int index=1;index<colspan;index++){
									ac1.add(getPostion(x1+index,y1));
								}
								
								for(int index=1;index<rowspan;index++){
									ac1.add(getPostion(x1,y1+index));
								}
							}
							
							//5.新建單元格
							ac1.add(getPostion(x1,y1));//註冊
							WritableCellFormat format = new WritableCellFormat();
							loadStyleConfig(format,temp);
							Label lb = new Label(x1, y1, StringHelper.getValue(td.getNodeValue()).trim(),format);
							colsMap.put(x1,StringHelper.getValue(td.getNodeValue()).trim());
							sheet.addCell(lb);
							
							x1 = x1 + colspan ;
						}
						sheet.setRowView(y1,400);
					}
					y1++;
				}
			}
			this.gcolsMap.put(tableTag.getId(),colsMap);
		}catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	
	/**
	 * @param tbodyTag
	 * @param tbody
	 * 导出表的主体内容
	 */
	@SuppressWarnings("unused")
	public void exportTbody(TbodyTag tbodyTag,Tbody tbody){
		if(tbodyTag == null)
			return ;
		
		if(false){//采用odbc 方式导出 暂时不采用
			try{
				TableTag tableTag = tbodyTag.getTableTag();
				ExportTag exportTag = tableTag.getExportTag();
				Map<Integer,String> colsMap = this.gcolsMap.get(tableTag.getId());
				if(colsMap == null) return ;
				Map<Integer,BaseDataEntity> columnsConfig = tableTag.getColumnsConfig();//表格列配置
				BaseDataEntity tableConfig = tableTag.getTable().getExportConfig(); //表配置
				
				//循環tbody 取出其中的tr
				for (int j = 0;tbody.getItems() != null && j < tbody.getItems().size(); j++) {
					if(tbody.getItems().get(j) instanceof Tr){
						Tr tr = (Tr)tbody.getItems().get(j);//tr
						BaseDataEntity trConfig = tr.getExportConfig();//tr配置
						tableConfig.CopyTo(trConfig,false,false);
							
						x1 = ox1;
						StringBuffer sql1 = new StringBuffer();
						sql1.append("insert into [").append(exportTag.GetExtValue("sheetname","Sheet1")).append("$] ");
						//sql1.append(" (");
						StringBuffer sql2 = new StringBuffer();
						sql2.append(" values(");
						boolean bfirst = true;
						for (int k = 0;tr.getItems() != null && k < tr.getItems().size(); k++) {
							if(tr.getItems().get(k) instanceof Td){
								Td td = (Td)tr.getItems().get(k);//td
								//1. 判斷 x ,y 座標有沒有補占用,並計算出新的x,y座標
								//int[] ay = calPostion(ac,x1,y1);
								//x1 = ay[0];
								//y1 = ay[1];
								
								//2 判斷是否需要合併單元格
								int colspan = td.getColspan();
								int rowspan = td.getRowspan();
								if(colspan > 1 || rowspan >1){
									for(int index=1;index<colspan;index++){
										ac1.add(getPostion(x1+index,y1));
									}
									
									for(int index=1;index<rowspan;index++){
										ac1.add(getPostion(x1,y1+index));
									}
								}
								
								//3.新建單元格
								ac1.add(getPostion(x1,y1));//註冊
								//x1, td.getNodeValue()).trim().replaceAll("&nbsp;","")
								if(!bfirst){
									//sql1.append(" , ");
									sql2.append(" , ");
								}else{
									bfirst = false;
								}
								//sql1.append(colsMap.get(x1));
								sql2.append("'").append(StringHelper.getValue(td.getNodeValue()).trim().replaceAll("&nbsp;","")).append("'");
								
								
								x1 = x1 + colspan ;
							}
						}
						//sql1.append(")");
						sql2.append(")");
						try{
							this.stmnt.executeUpdate(sql1.toString()+sql2.toString());
							//this.stmnt.close();
							//System.out.println("export time:"+(System.currentTimeMillis() - cur) / 1000.0);
							//cur = System.currentTimeMillis();
						}catch (Exception e) {
							e.printStackTrace();
						}
						y1++;
					}
				}
			}catch (Exception e) {
				e.printStackTrace();
			}
		}else{
			try{
				TableTag tableTag = tbodyTag.getTableTag();
				ExportTag exportTag = tableTag.getExportTag();
				WritableSheet sheet = wb1.getSheet(exportTag.GetExtValue("sheetname",""));
				curSheetName = exportTag.GetExtValue("sheetname","");
				
				Map<Integer,BaseDataEntity> columnsConfig = tableTag.getColumnsConfig();//表格列配置
				BaseDataEntity tableConfig = tableTag.getTable().getExportConfig(); //表配置
				
				//循環tbody 取出其中的tr
				for (int j = 0;tbody.getItems() != null && j < tbody.getItems().size(); j++) {
					if(tbody.getItems().get(j) instanceof Tr){
						Tr tr = (Tr)tbody.getItems().get(j);//tr
						BaseDataEntity trConfig = tr.getExportConfig();//tr配置
						tableConfig.CopyTo(trConfig,false,false);
							
						x1 = ox1;
						
						if(y1-1 >= 0){
							for (int i = 0; i <= tr.getItems().size(); i++) {
								ac1.remove(getPostion(i,y1-1));
							}
						}
						
						//处理tr高度
						int heigth = 0;
						heigth = trConfig.GetParamIntValue("height",0);
						
						
						for (int k = 0;tr.getItems() != null && k < tr.getItems().size(); k++) {
							if(tr.getItems().get(k) instanceof Td){
								Td td = (Td)tr.getItems().get(k);//td
								//1. 判斷 x ,y 座標有沒有補占用,並計算出新的x,y座標
								int[] ay = calPostion(ac1,x1,y1);
								x1 = ay[0];
								y1 = ay[1];
								
								if(heigth > 0){
									sheet.setRowView(y1,heigth);
								}
								
								//2 判斷是否需要合併單元格
								int colspan = td.getColspan();
								int rowspan = td.getRowspan();
								if(colspan > 1 || rowspan >1){
									sheet.mergeCells(x1,y1,x1+colspan-1,y1+rowspan-1);
									for(int index=1;index<colspan;index++){
										ac1.add(getPostion(x1+index,y1));
									}
									
									for(int index=1;index<rowspan;index++){
										ac1.add(getPostion(x1,y1+index));
									}
								}
								
								//3.新建單元格
								ac1.add(getPostion(x1,y1));//註冊
								//取得td config ,自身的config要與表定義的config融合
								BaseDataEntity tdConfig = td.getExportConfig();
								trConfig.CopyTo(tdConfig,false,false);
								if(columnsConfig.get(x1) != null)
									columnsConfig.get(x1).CopyTo(tdConfig,false,false);
								
								if(tdConfig == null){
									WritableCell cell = new Label(x1, y1, StringHelper.getValue(td.getNodeValue()).trim().replaceAll("&nbsp;",""));
									sheet.addCell(cell);
								}else{
									//3处理td宽度
									int width = 0;
									width = tdConfig.GetParamIntValue("width",0);
									if(width > 0){
										sheet.setColumnView(x1,width);
									}
									
									if("img".equals(tdConfig.gpsv("type"))){
										WritableImage img = getImage(x1,y1,td,tdConfig);
										if(img != null)
											sheet.addImage(img);
									}else{
										WritableCell cell = getCell(x1,y1,td,tdConfig);
										sheet.addCell(cell);
									}
								}
								
								
								x1 = x1 + colspan ;
							}
						}
						y1++;
					}
				}
			}catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void exportTfoot(TfootTag tfootTag,Tfoot tfoot){
		if(tfootTag == null)
			return ;
		try{
			TableTag tableTag = tfootTag.getTableTag();
			ExportTag exportTag = tableTag.getExportTag();
			WritableSheet sheet = wb1.getSheet(exportTag.GetExtValue("sheetname",""));
			curSheetName = exportTag.GetExtValue("sheetname","");
			
			Map<Integer,BaseDataEntity> columnsConfig = tableTag.getColumnsConfig();//表格列配置
			BaseDataEntity tableConfig = tableTag.getTable().getExportConfig(); //表配置
			
	//		循環thead 取出其中的tr
			for (int j = 0;tfoot.getItems() != null && j < tfoot.getItems().size(); j++) {
				if(tfoot.getItems().get(j) instanceof Tr){
					Tr tr = (Tr)tfoot.getItems().get(j);//tr
					BaseDataEntity trConfig = null; //tr配置
					if(!StringHelper.IsNullOrEmpty(tr.GetExtValue("config",""))){
						trConfig = BaseDataEntity.FromJSONString(StringHelper.Format("{%1$s}",tr.GetExtValue("config","")));
					}
					if(trConfig == null)
						trConfig = tableConfig;
					else if(tableConfig != null){
						tableConfig.CopyTo(trConfig,false);
					}
					
					x1 = ox1;
					if(y1-1 >= 0){
						for (int i = 0; i <= tr.getItems().size(); i++) {
							ac1.remove(getPostion(i,y1-1));
						}
					}
					for (int k = 0;tr.getItems() != null && k < tr.getItems().size(); k++) {
						if(tr.getItems().get(k) instanceof Td){
							Td td = (Td)tr.getItems().get(k);//td
							//1. 判斷 x ,y 座標有沒有補占用,並計算出新的x,y座標
							int[] ay = calPostion(ac1,x1,y1);
							x1 = ay[0];
							y1 = ay[1];
							
							//2 判斷是否需要合併單元格
							int colspan = td.getColspan();
							int rowspan = td.getRowspan();
							if(colspan > 1 || rowspan >1){
								sheet.mergeCells(x1,y1,x1+colspan-1,y1+rowspan-1);
								for(int index=1;index<colspan;index++){
									ac1.add(getPostion(x1+index,y1));
								}
								
								for(int index=1;index<rowspan;index++){
									ac1.add(getPostion(x1,y1+index));
								}
							}
							
							//3.新建單元格
							ac1.add(getPostion(x1,y1));//註冊
							//取得td config ,自身的config要與表定義的config融合
							BaseDataEntity tdConfig = td.getExportConfig();
							trConfig.CopyTo(tdConfig,false,false);
							columnsConfig.get(x1).CopyTo(tdConfig,false,false);
							
							if(tdConfig == null){
								WritableCell cell = new Label(x1, y1, StringHelper.getValue(td.getNodeValue()).trim().replaceAll("&nbsp;",""));
								sheet.addCell(cell);
							}else{
								if("img".equals(tdConfig.gpsv("type"))){
									WritableImage img = getImage(x1,y1,td,tdConfig);
									if(img != null)
										sheet.addImage(img);
								}else{
									WritableCell cell = getCell(x1,y1,td,tdConfig);
									sheet.addCell(cell);
								}
							}
							
							x1 = x1 + colspan ;
						}
					}
					y1++;
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	public void closeFile(){
		if(this.wb1 != null){
			try {
				wb1.write();
				wb1.close();
				wb1 = null;
			} catch (Exception e) {
				e.printStackTrace();
			}
			
		}
	}
	
	
	public void init(BasePage page){
		this.page = page;
	}
	
	public void Export(){
		if(this.page.isDebug())
			System.out.println("export time:"+(System.currentTimeMillis() - cur) / 1000.0);
		if(this.wb1 != null){
			this.closeFile();
			onExportExcelByFile();
		}else if("xls".equalsIgnoreCase(page.getExportType())){
			onExportExcel();
		}
	}
	
	public void onExportExcelByFile(){
		BufferedInputStream bis = null;
		BufferedOutputStream bos = null;
		try
		{
			page.getResponse().reset();
			page.getResponse().setContentType("application/vnd.ms-excel;charset=UTF-8");
			page.getResponse().addHeader("Content-Disposition", StringHelper.Format("filename="+"%1$s.xls",page.getExportFileName()));
			
			bis = new BufferedInputStream(new FileInputStream(this.fileName.toString()));
			bos = new BufferedOutputStream(page.getResponse().getOutputStream());

			byte[] buff = new byte[2048];
			int bytesRead;

			while (-1 != (bytesRead = bis.read(buff, 0, buff.length)))
			{
				bos.write(buff, 0, bytesRead);
			}

		}
		catch (final IOException e)
		{
			System.out.println("出现IOException." + e);
		}
		finally
		{
			if (bis != null)
				try {
					bis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			if (bos != null)
				try {
					bos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
		}
	}
	
	public void onExportExcel(){
		nodes = page.getExportNodes();
		if(nodes == null) return ;
		try {
			
			
			OutputStream os = page.getResponse().getOutputStream();
//			WorkbookSettings ws = new WorkbookSettings();
//	        ws.setUseTemporaryFileDuringWrite(true);
	        WritableWorkbook wb = Workbook.createWorkbook(os);
	        
	        int x = 0;//x 軸
	        int y = 0;//y 軸
	        
	        int ox = 0;//x 起點座標
	        int oy = 0;//y 起點座標
	        Vector<String> ac = new Vector<String>() ;//All Cell 記錄哪些單點格被占用
	        for(Export node : nodes){
	        	WritableSheet sheet = wb.getSheet(node.getSheetName()); 
	        	curSheetName = node.getSheetName();
	        	if(sheet == null){
	        		sheet = wb.createSheet(node.getSheetName(),wb.getSheets().length);
	        	}
	        	
	        	BaseDataEntity data = node.getExportConfig();
	        	x = data.GetParamIntValue("x",sheet.getColumns());
	        	y = data.GetParamIntValue("y",sheet.getRows());
	        	
	        	ox = data.GetParamIntValue("x",sheet.getColumns());
	        	oy = data.GetParamIntValue("y",sheet.getRows());
	        	
	        	if(node.getItems() != null && node.getItems().size() > 0){
	        		for (int i = 0; i < node.getItems().size(); i++) {
						FormItem object = node.getItems().get(i);
						if(object instanceof Table){
							Table table = (Table)object;
							Map<Integer,BaseDataEntity> columnsConfig = new HashMap<Integer,BaseDataEntity>();//表格列配置
							BaseDataEntity tableConfig = table.getExportConfig(); //表配置
							
							//處理表頭
							if(table.getThead() != null){
								Thead head = table.getThead();
								//循環thead 取出其中的tr
								for (int j = 0;head.getItems() != null && j < head.getItems().size(); j++) {
									if(head.getItems().get(j) instanceof Tr){
										Tr tr = (Tr)head.getItems().get(j);//tr
										BaseDataEntity trConfig = tr.getExportConfig();//tr配置
										tableConfig.CopyTo(trConfig,false,false);
										
										x = ox;
										if(y-1 >= 0){
											for (int k = 0; k <= tr.getItems().size(); k++) {
												ac.remove(getPostion(k,y-1));
											}
										}
										for (int k = 0;tr.getItems() != null && k < tr.getItems().size(); k++) {
											if(tr.getItems().get(k) instanceof Td){
												Td td = (Td)tr.getItems().get(k);//td
												
												//1. 判斷 x ,y 座標有沒有補占用,並計算出新的x,y座標
												int[] ay = calPostion(ac,x,y);
												x = ay[0];
												y = ay[1];
												
												//2 處理表格配置
												BaseDataEntity tdConfig = td.getExportConfig();
												
												//3处理td宽度
												int width = 0;
												width = tdConfig.GetParamIntValue("width",0);
												if(width > 0){
													sheet.setColumnView(x,width);
												}
												
												for(int index=0;index<td.getColspan();index++){
													columnsConfig.put(x+index,tdConfig);
												}
												
												//新建临时配置 原tdconfig是公用的所以不能修改
												BaseDataEntity temp = new BaseDataEntity();
												tdConfig.CopyTo(temp,true,false);
												trConfig.CopyTo(temp,false,false);
												
												//4 判斷是否需要合併單元格
												int colspan = td.getColspan();
												int rowspan = td.getRowspan();
												if(colspan > 1 || rowspan >1){
													sheet.mergeCells(x,y,x+colspan-1,y+rowspan-1);
													for(int index=1;index<colspan;index++){
														ac.add(getPostion(x+index,y));
													}
													
													for(int index=1;index<rowspan;index++){
														ac.add(getPostion(x,y+index));
													}
												}
												
												//5.新建單元格
												ac.add(getPostion(x,y));//註冊
												WritableCellFormat format = new WritableCellFormat();
												loadStyleConfig(format,temp);
												Label lb = new Label(x, y, StringHelper.getValue(td.getNodeValue()).trim(),format);
												sheet.addCell(lb);
												
												x = x + colspan ;
											}
										}
										y++;
									}
								}
							}
							
							//處理表身
							if(table.getTbody() != null){
								Tbody body = table.getTbody();
								//循環tbody 取出其中的tr
								for (int j = 0;body.getItems() != null && j < body.getItems().size(); j++) {
									if(body.getItems().get(j) instanceof Tr){
										Tr tr = (Tr)body.getItems().get(j);//tr
										BaseDataEntity trConfig = tr.getExportConfig();//tr配置
										tableConfig.CopyTo(trConfig,false,false);
											
										x = ox;
										if(y-1 >= 0){
											for (int k = 0; k <= tr.getItems().size(); k++) {
												ac.remove(getPostion(k,y-1));
											}
										}
										for (int k = 0;tr.getItems() != null && k < tr.getItems().size(); k++) {
											if(tr.getItems().get(k) instanceof Td){
												Td td = (Td)tr.getItems().get(k);//td
												//1. 判斷 x ,y 座標有沒有補占用,並計算出新的x,y座標
												int[] ay = calPostion(ac,x,y);
												x = ay[0];
												y = ay[1];
												
												//2 判斷是否需要合併單元格
												int colspan = td.getColspan();
												int rowspan = td.getRowspan();
												if(colspan > 1 || rowspan >1){
													sheet.mergeCells(x,y,x+colspan-1,y+rowspan-1);
													for(int index=1;index<colspan;index++){
														ac.add(getPostion(x+index,y));
													}
													
													for(int index=1;index<rowspan;index++){
														ac.add(getPostion(x,y+index));
													}
												}
												
												//3.新建單元格
												ac.add(getPostion(x,y));//註冊
												//取得td config ,自身的config要與表定義的config融合
												BaseDataEntity tdConfig = td.getExportConfig();
												trConfig.CopyTo(tdConfig,false,false);
												columnsConfig.get(x).CopyTo(tdConfig,false,false);
												
												if(tdConfig == null){
													WritableCell cell = new Label(x, y, StringHelper.getValue(td.getNodeValue()).trim().replaceAll("&nbsp;",""));
													sheet.addCell(cell);
												}else{
													if("img".equals(tdConfig.gpsv("type"))){
														WritableImage img = getImage(x,y,td,tdConfig);
														if(img != null)
															sheet.addImage(img);
													}else{
														WritableCell cell = getCell(x,y,td,tdConfig);
														sheet.addCell(cell);
													}
												}
												
												x = x + colspan ;
											}
										}
										y++;
									}
								}
							}
							
							//處理表尾
							if(table.getTfoot() != null){
								Tfoot foot = table.getTfoot();
								//循環thead 取出其中的tr
								for (int j = 0;foot.getItems() != null && j < foot.getItems().size(); j++) {
									if(foot.getItems().get(j) instanceof Tr){
										Tr tr = (Tr)foot.getItems().get(j);//tr
										BaseDataEntity trConfig = null; //tr配置
										if(!StringHelper.IsNullOrEmpty(tr.GetExtValue("config",""))){
											trConfig = BaseDataEntity.FromJSONString(StringHelper.Format("{%1$s}",tr.GetExtValue("config","")));
										}
										if(trConfig == null)
											trConfig = tableConfig;
										else if(tableConfig != null){
											tableConfig.CopyTo(trConfig,false);
										}
										
										x = ox;
										if(y-1 >= 0){
											for (int k = 0; k <= tr.getItems().size(); k++) {
												ac.remove(getPostion(k,y-1));
											}
										}
										for (int k = 0;tr.getItems() != null && k < tr.getItems().size(); k++) {
											if(tr.getItems().get(k) instanceof Td){
												Td td = (Td)tr.getItems().get(k);//td
												//1. 判斷 x ,y 座標有沒有補占用,並計算出新的x,y座標
												int[] ay = calPostion(ac,x,y);
												x = ay[0];
												y = ay[1];
												
												//2 判斷是否需要合併單元格
												int colspan = td.getColspan();
												int rowspan = td.getRowspan();
												if(colspan > 1 || rowspan >1){
													sheet.mergeCells(x,y,x+colspan-1,y+rowspan-1);
													for(int index=1;index<colspan;index++){
														ac.add(getPostion(x+index,y));
													}
													
													for(int index=1;index<rowspan;index++){
														ac.add(getPostion(x,y+index));
													}
												}
												
												//3.新建單元格
												ac.add(getPostion(x,y));//註冊
												//取得td config ,自身的config要與表定義的config融合
												BaseDataEntity tdConfig = td.getExportConfig();
												trConfig.CopyTo(tdConfig,false,false);
												columnsConfig.get(x).CopyTo(tdConfig,false,false);
												
												if(tdConfig == null){
													WritableCell cell = new Label(x, y, StringHelper.getValue(td.getNodeValue()).trim().replaceAll("&nbsp;",""));
													sheet.addCell(cell);
												}else{
													if("img".equals(tdConfig.gpsv("type"))){
														WritableImage img = getImage(x1,y1,td,tdConfig);
														if(img != null)
															sheet.addImage(img);
													}else{
														WritableCell cell = getCell(x,y,td,tdConfig);
														sheet.addCell(cell);
													}
												}
												
												x = x + colspan ;
											}
										}
										y++;
									}
								}
							}
							
						}
					}
	        	}
	        }
	        
			wb.write();
			wb.close();
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * @param x
	 * @param y
	 * @return
	 * 格式化x,y軸
	 */
	public String getPostion(int x,int y){
		return StringHelper.Format("%1$s-%2$s-%3$s",x,y,curSheetName);
	}
	
	/**
	 * @param vector
	 * @param x
	 * @param y
	 * @return
	 * 重新計算x,y軸座標
	 */
	public int[] calPostion(Vector vector,int x,int y){
		String key = getPostion(x,y);
		while(vector.contains(key)){
			x++;
			key = getPostion(x,y);
		}
		int[] ay = {x,y};
		return ay;
	}
	
	/**
	 * @param strFormat
	 * @return
	 * 取数字格式
	 */
	public DisplayFormat getNumberFormat(String strFormat){
		if(this.numberMap == null)
			this.numberMap = new HashMap<String ,DisplayFormat>();
		if(this.numberMap.containsKey(strFormat))
			return this.numberMap.get(strFormat);
		
		
		DisplayFormat format = null;
		if("#".equals(strFormat)){
			format = NumberFormats.DEFAULT; 
		}else{
			format = new NumberFormat(strFormat);
		}
		this.numberMap.put(strFormat,format);
		return format;
	} 
	
	/**
	 * @param arg
	 * @return
	 * 取边框位置
	 */
	public Border getBorder(String arg){
		Border border = null;
		if("none".equalsIgnoreCase(arg)){
			border = Border.NONE;
		}else if("all".equalsIgnoreCase(arg)){
			border = Border.ALL;
		}else if("top".equalsIgnoreCase(arg)){
			border = Border.TOP;
		}else if("right".equalsIgnoreCase(arg)){
			border = Border.RIGHT;
		}else if("bottom".equalsIgnoreCase(arg)){
			border = Border.BOTTOM;
		}else if("left".equalsIgnoreCase(arg)){
			border = Border.LEFT;
		}
		return border;
	}
	
	/**
	 * @param arg
	 * @return
	 * 取边框线条的样式
	 */
	public BorderLineStyle getBorderLineStyle(String arg){
		BorderLineStyle line = null;
		if("none".equalsIgnoreCase(arg)){
			line = BorderLineStyle.NONE;
		}else if("thin".equalsIgnoreCase(arg)){
			line = BorderLineStyle.THIN;
		}else if("medium".equalsIgnoreCase(arg)){
			line = BorderLineStyle.MEDIUM;
		}else if("dashed".equalsIgnoreCase(arg)){
			line = BorderLineStyle.DASHED;
		}else if("dotted".equalsIgnoreCase(arg)){
			line = BorderLineStyle.DOTTED;
		}else if("thick".equalsIgnoreCase(arg)){
			line = BorderLineStyle.THICK;
		}else if("double".equalsIgnoreCase(arg)){
			line = BorderLineStyle.DOUBLE;
		}else if("hair".equalsIgnoreCase(arg)){
			line = BorderLineStyle.HAIR;
		}else if("medium_dashed".equalsIgnoreCase(arg)){
			line = BorderLineStyle.MEDIUM_DASHED;
		}else if("dash_dot".equalsIgnoreCase(arg)){
			line = BorderLineStyle.DASH_DOT;
		}else if("medium_dash_dot".equalsIgnoreCase(arg)){
			line = BorderLineStyle.MEDIUM_DASH_DOT;
		}else if("dash_dot_dot".equalsIgnoreCase(arg)){
			line = BorderLineStyle.DASH_DOT_DOT;
		}else if("medium_dash_dot_dot".equalsIgnoreCase(arg)){
			line = BorderLineStyle.MEDIUM_DASH_DOT_DOT;
		}else if("slanted_dash_dot".equalsIgnoreCase(arg)){
			line = BorderLineStyle.SLANTED_DASH_DOT;
		}else {
			line = BorderLineStyle.THIN;
		}
		return line;
	}
	
	/**
	 * @param arg
	 * @return
	 * 颜色
	 */
	public Colour getColor(String arg){
		if("unknown".equals(arg)){
			return Colour.UNKNOWN;
		}else if("black".equals(arg)){
			return Colour.BLACK;
		}else if("white".equals(arg)){
			return Colour.WHITE;
		}else if("default_background1".equals(arg)){
			return Colour.DEFAULT_BACKGROUND1;
		}else if("default_background".equals(arg)){
			return Colour.DEFAULT_BACKGROUND;
		}else if("palette_black".equals(arg)){
			return Colour.PALETTE_BLACK;
		}else if("red".equals(arg)){
			return Colour.RED;
		}else if("bright_green".equals(arg)){
			return Colour.BRIGHT_GREEN;
		}else if("blue".equals(arg)){
			return Colour.BLUE;
		}else if("pink".equals(arg)){
			return Colour.PINK;
		}else if("turquoise".equals(arg)){
			return Colour.TURQUOISE;
		}else if("dark_red".equals(arg)){
			return Colour.DARK_RED;
		}else if("green".equals(arg)){
			return Colour.GREEN;
		}else if("dark_blue".equals(arg)){
			return Colour.DARK_BLUE;
		}else if("dark_yellow".equals(arg)){
			return Colour.DARK_YELLOW;
		}else if("violet".equals(arg)){
			return Colour.VIOLET;
		}else if("teal".equals(arg)){
			return Colour.TEAL;
		}else if("grey_25_percent".equals(arg)){
			return Colour.GREY_25_PERCENT;
		}else if("grey_50_percent".equals(arg)){
			return Colour.GREY_50_PERCENT;
		}else if("periwinkle".equals(arg)){
			return Colour.PERIWINKLE;
		}else if("plum2".equals(arg)){
			return Colour.PLUM2;
		}else if("ivory".equals(arg)){
			return Colour.IVORY;
		}else if("light_turquoise2".equals(arg)){
			return Colour.LIGHT_TURQUOISE2;
		}else if("dark_purple".equals(arg)){
			return Colour.DARK_PURPLE;
		}else if("coral".equals(arg)){
			return Colour.CORAL;
		}else if("ocean_blue".equals(arg)){
			return Colour.OCEAN_BLUE;
		}else if("ice_blue".equals(arg)){
			return Colour.ICE_BLUE;
		}else if("dark_blue2".equals(arg)){
			return Colour.DARK_BLUE2;
		}else if("pink2".equals(arg)){
			return Colour.PINK2;
		}else if("yellow2".equals(arg)){
			return Colour.YELLOW2;
		}else if("turqoise2".equals(arg)){
			return Colour.TURQOISE2;
		}else if("violet2".equals(arg)){
			return Colour.VIOLET2;
		}else if("dark_red2".equals(arg)){
			return Colour.DARK_RED2;
		}else if("teal2".equals(arg)){
			return Colour.TEAL2;
		}else if("blue2".equals(arg)){
			return Colour.BLUE2;
		}else if("sky_blue".equals(arg)){
			return Colour.SKY_BLUE;
		}else if("light_turquoise".equals(arg)){
			return Colour.LIGHT_TURQUOISE;
		}else if("light_green".equals(arg)){
			return Colour.LIGHT_GREEN;
		}else if("very_light_yellow".equals(arg)){
			return Colour.VERY_LIGHT_YELLOW;
		}else if("pale_blue".equals(arg)){
			return Colour.PALE_BLUE;
		}else if("rose".equals(arg)){
			return Colour.ROSE;
		}else if("lavender".equals(arg)){
			return Colour.LAVENDER;
		}else if("tan".equals(arg)){
			return Colour.TAN;
		}else if("light_blue".equals(arg)){
			return Colour.LIGHT_BLUE;
		}else if("aqua".equals(arg)){
			return Colour.AQUA;
		}else if("lime".equals(arg)){
			return Colour.LIME;
		}else if("gold".equals(arg)){
			return Colour.GOLD;
		}else if("light_orange".equals(arg)){
			return Colour.LIGHT_ORANGE;
		}else if("orange".equals(arg)){
			return Colour.ORANGE;
		}else if("blue_grey".equals(arg)){
			return Colour.BLUE_GREY;
		}else if("grey_40_percent".equals(arg)){
			return Colour.GREY_40_PERCENT;
		}else if("dark_teal".equals(arg)){
			return Colour.DARK_TEAL;
		}else if("sea_green".equals(arg)){
			return Colour.SEA_GREEN;
		}else if("dark_green".equals(arg)){
			return Colour.DARK_GREEN;
		}else if("olive_green".equals(arg)){
			return Colour.OLIVE_GREEN;
		}else if("brown".equals(arg)){
			return Colour.BROWN;
		}else if("plum".equals(arg)){
			return Colour.PLUM;
		}else if("indigo".equals(arg)){
			return Colour.INDIGO;
		}else if("grey_80_percent".equals(arg)){
			return Colour.GREY_80_PERCENT;
		}else if("automatic".equals(arg)){
			return Colour.AUTOMATIC;
		}else if("gray_80".equals(arg)){
			return Colour.GRAY_80;
		}else if("gray_50".equals(arg)){
			return Colour.GRAY_50;
		}else if("gray_25".equals(arg)){
			return Colour.GRAY_25;
		}
		
		return Colour.BLACK;
	}
	
	/**
	 * @return
	 * 水平对齐方式
	 */
	public Alignment getAlign(String strAlign){
		if("center".equalsIgnoreCase(strAlign))
			return Alignment.CENTRE;
		
		if("left".equalsIgnoreCase(strAlign))
			return Alignment.LEFT;
		
		if("right".equalsIgnoreCase(strAlign))
			return Alignment.RIGHT;
		
		if("fill".equalsIgnoreCase(strAlign))
			return Alignment.FILL;
		
		if("general".equalsIgnoreCase(strAlign))
			return Alignment.GENERAL;
			
		if("justify".equalsIgnoreCase(strAlign))
			return Alignment.JUSTIFY;
		
		return Alignment.LEFT;
	}
	
	/**
	 * @param strValign
	 * @return
	 * 取垂直对齐方式
	 */
	public VerticalAlignment getValign(String strValign){
		if("top".equalsIgnoreCase(strValign))
			return VerticalAlignment.TOP;
		if("bottom".equalsIgnoreCase(strValign))
			return VerticalAlignment.BOTTOM;
		if("center".equalsIgnoreCase(strValign))
			return VerticalAlignment.CENTRE;
		if("justify".equalsIgnoreCase(strValign))
			return VerticalAlignment.JUSTIFY;
		
		return VerticalAlignment.CENTRE;
	}
	
	/**
	 * @param strFont
	 * @return
	 * 字体
	 */
	public WritableFont getFont(String strFont){
		if("arial".equalsIgnoreCase(strFont))
			return new WritableFont(WritableFont.ARIAL);
		
		if("times".equalsIgnoreCase(strFont))
			return new WritableFont(WritableFont.TIMES);
		
		if("courier".equalsIgnoreCase(strFont))
			return new WritableFont(WritableFont.ARIAL);
		
		if("tahoma".equalsIgnoreCase(strFont))
			return new WritableFont(WritableFont.ARIAL);
		
		return new WritableFont(WritableFont.ARIAL);
	}
	
	
	public void loadStyleConfig(WritableCellFormat format,BaseDataEntity config){
		try {
			//1.边框
			try{
				if(config.isNullOrEmpty("border")){
					config.SetParamValue("border","all thin black");
				}
				
				String strBorder = config.gpsv("border","all thin black");
				String ay[] = strBorder.split(" ");
				String arg1 = "all";
				String arg2 = "thin";
				String arg3 = "black";
				
				for (int i = 0; i < ay.length && i<3; i++) {
					if(i == 0){//位置
						arg1 = ay[i];
					}else if(i == 1){//宽度
						arg2 = ay[i];
					}else if(i == 2){//颜色
						arg3 = ay[i];
					}
				}
				format.setBorder(getBorder(arg1),getBorderLineStyle(arg2),getColor(arg3));
			}catch (Exception e) {}
			
			//2.水平对齐方式
			if(!config.isNullOrEmpty("align")){
				format.setAlignment(getAlign(config.gpsv("align")));
			}
			
			//2.垂直对齐方式
			format.setVerticalAlignment(getValign(config.gpsv("valign")));
			
			//3.背景色
			if(!config.isNullOrEmpty("bgcolor"))
				format.setBackground(getColor(config.gpsv("bgcolor")));
			
			//4.字体相关
			//WritableFont font = new WritableFont(WritableFont.ARIAL,20,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
			WritableFont font = getFont(config.gpsv("font"));
			font.setPointSize(config.GetParamIntValue("font-size",12));

			if(config.isNullOrEmpty("font-weight") || "normal".equalsIgnoreCase(config.gpsv("font-weight")))
				font.setBoldStyle(WritableFont.NO_BOLD);
			else 
				font.setBoldStyle(WritableFont.BOLD);
			
			font.setColour(getColor(config.gpsv("font-color")));
			format.setFont(font);
			
			//5.自动换行
			format.setWrap(config.GetParamBoolValue("wrap",false));
		} catch (Exception e) {
		}
	}
	
	/**
	 * @param x
	 * @param y
	 * @param value
	 * @param config
	 * @return
	 * 獲取單元格
	 */
	public WritableCell getCell(int x,int y,Td td,BaseDataEntity config){
		String value = StringHelper.getValue(td.getNodeValue()).trim().replaceAll("&nbsp;","");
		WritableCell cell = null ;
		WritableCellFormat format = null;
		
		if(config == null || StringHelper.IsNullOrEmpty(config.gpsv("type"))){
			format = new WritableCellFormat();
			loadStyleConfig(format,config);
			return new Label(x, y, value,format);
		}
		try{
			if("number".equals(config.gpsv("type")) && !StringHelper.IsNullOrEmpty(value)){
				new WritableCellFormat();
				format = new WritableCellFormat(this.getNumberFormat(config.gpsv("format","#")));
				loadStyleConfig(format,config);
				cell = new Number(x,y,Double.parseDouble(value),format);
				return cell;	
			}else if("date".equals(config.gpsv("type")) && !StringHelper.IsNullOrEmpty(value)){
				DateFormat df = new DateFormat(config.gpsv("format","yyyy/MM/dd"));
				format = new WritableCellFormat(df);
				loadStyleConfig(format,config);
				SimpleDateFormat sf = new SimpleDateFormat(config.gpsv("format","yyyy/MM/dd"));
				cell = new DateTime(x,y,sf.parse(StringHelper.getValue(value,"0")),format);
				return cell;
			}
		}catch (Exception e) {
			//e.printStackTrace();
		}
		
		format = new WritableCellFormat();
		loadStyleConfig(format,config);
		cell = new Label(x, y, value,format);
		return cell;
	}
	
	public WritableImage getImage(int x,int y,Td td,BaseDataEntity config){
		try{
			if(!config.isNullOrEmpty("url")){
				String url = page.getRequest().getRequestURL().toString().replace(page.getRequest().getRequestURI(), "")+config.gpsv("url");
				byte[] data = this.getURLImgData(url);
				if(data != null){
					return new WritableImage(x,y,1,1,data);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public WritableWorkbook getWb1() {
		return wb1;
	}

	public StringBuffer getFileName() {
		return fileName;
	}
	
	public byte[] getURLImgData(String url) {
		byte[] data = null;
		try{
			HttpClient client = new HttpClient(); // HTTPClient.
	        String encoing = "UTF-8"; // charset encoding.
	        GetMethod get = new GetMethod(url);
	        
//	        StringBuffer sb = new StringBuffer();
//	        Cookie[] cks = page.getRequest().getCookies();
//	        for (int i = 0; i < 1; i++) {
//	        	Cookie ck = cks[i];
//	        	sb.append("&").append(ck.getName()).append("=").append(ck.getValue());
//			}
//	        System.out.println(sb.toString());
//	        get.setRequestHeader("Cookie", sb.toString());
	        client.executeMethod(get);
//	       System.out.println(get.getResponseBodyAsString());
	        data = get.getResponseBody();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return data;
    }
}
